package com.credithc.customers.base.mapper;

import com.credithc.baseserv.core.mapper.BsBaseMapper;
import com.credithc.customers.base.po.CustUnifyRegisterStatisticPo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Title:
 * @Author: fby
 * @Date: 2020/6/9 14:06
 * @Description: com.credithc.customers.base.mapper
 * @Version: V1.0.0
 */
@Mapper
@Repository
public interface CustUnifyRegisterStatisticMapper extends BsBaseMapper<CustUnifyRegisterStatisticPo> {
    @Select({"<script>",
            "SELECT sub_system_sign AS subSystemSign,sum(auth_login_num) AS authLoginNum,sum(register_num) AS registerNum,sum(auth_register_num) AS authRegisterNum,#{today} AS date,hour FROM",
            "cust_unify_register_statistic",
            "WHERE date <![CDATA[>]]> #{yesterDay} AND date <![CDATA[<=]]> #{today} AND type=0",
            "<when test='subSystemSign!=null'>",
            "AND sub_system_sign = #{subSystemSign}",
            "</when>",
            "GROUP BY sub_system_sign,hour",
            "</script>"})
    List<CustUnifyRegisterStatisticPo> getRegisterStatistic(@Param("today") String today, @Param("yesterDay") String yesterDay, @Param("subSystemSign") String subSystemSign);

    @Select({"<script>",
            "SELECT sub_system_sign AS subSystemSign,sum(auth_login_num) AS authLoginNum,sum(register_num) AS registerNum,sum(auth_register_num) AS authRegisterNum,#{today} AS date,total_num AS totalNum,hour FROM",
            "cust_unify_register_statistic",
            "WHERE date <![CDATA[>]]> #{yesterDay} AND date <![CDATA[<=]]> #{today} AND type=1",
            "<when test='subSystemSign!=null'>",
            "AND sub_system_sign = #{subSystemSign}",
            "</when>",
            "GROUP BY sub_system_sign",
            "</script>"})
    List<CustUnifyRegisterStatisticPo> getRegisterStatisticSum(@Param("today") String today, @Param("yesterDay") String yesterDay, @Param("subSystemSign") String subSystemSign);

    @Select("SELECT A.subSystemSign, IFNULL(A.registerNum ,0) AS registerNum,IFNULL(B.authLoginNum ,0) AS authLoginNum,IFNULL(B.authRegisterNum ,0) AS authRegisterNum,STR_TO_DATE(#{today}, '%Y-%m-%d') AS date,current_timestamp() AS createTime , 0 AS type,A.hour" +
            " FROM ( SELECT MAX(registerNum) AS registerNum, subSystemSign,hour FROM (SELECT count(1) AS registerNum, sub_system_id AS subSystemSign,DATE_FORMAT(create_time, '%Y-%m-%d %H') AS hour FROM cust_oauth_token WHERE create_time BETWEEN #{yesterDay} AND #{today} GROUP BY sub_system_id,hour UNION " +
            "SELECT 0 AS registerNum, sub_system_id AS subSystemSign,DATE_FORMAT(modify_time, '%Y-%m-%d %H') AS hour FROM cust_oauth_token WHERE modify_time BETWEEN #{yesterDay} AND #{today} GROUP BY sub_system_id,hour) C GROUP BY C.subSystemSign,C.hour) A " +
            "LEFT JOIN ( SELECT count(1) AS authLoginNum, sum(`status` = 3) AS authRegisterNum, sub_system_sign AS subSystemSign,DATE_FORMAT(create_time, '%Y-%m-%d %H') AS hour FROM cust_oauth_login_flow WHERE create_time BETWEEN #{yesterDay} AND #{today} " +
            " GROUP BY sub_system_sign,hour ) B ON A.subSystemSign = B.subSystemSign AND A.hour=B.hour ORDER BY subSystemSign ASC,hour ASC"
    )
    List<CustUnifyRegisterStatisticPo> statisticRegister(@Param("today") String today, @Param("yesterDay") String yesterDay);

    @Select("SELECT a.subSystemSign,(IFNULL(a.oldTotalNum ,0)+IFNULL(b.registerNum ,0)) as totalNum,IFNULL(b.registerNum, 0) as registerNum,IFNULL(b.authLoginNum, 0) as authLoginNum,IFNULL(b.authRegisterNum, 0) as authRegisterNum,STR_TO_DATE(#{today}, '%Y-%m-%d') as date," +
            "current_timestamp() as createTime , 1 as type from(select total_num AS oldTotalNum,sub_system_sign as subSystemSign from " +
            "cust_unify_register_statistic where type=1 AND date = #{yesterDay} GROUP BY sub_system_sign) a LEFT JOIN (select sum(register_num) AS registerNum,sum(auth_register_num) AS authRegisterNum,sum(auth_login_num) AS authLoginNum,sub_system_sign AS subSystemSign from cust_unify_register_statistic\n" +
            " WHERE type=0 AND date = #{currentDay} GROUP BY sub_system_sign) b ON a.subSystemSign=b.subSystemSign"
    )
    List<CustUnifyRegisterStatisticPo> statisticRegisterSum(@Param("today") String today, @Param("yesterDayTime") String yesterDayTime, @Param("yesterDay") String yesterDay, @Param("currentDay") String currentDay);

}
